﻿Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.SQLite
Imports System.Windows.Forms

Public Class SQLiteDatabase
    Private dbConnection As String

    Property stringConnect() As String
        Get
            Return dbConnection
        End Get
        Set(ByVal value As String)
            Me.dbConnection = value
        End Set
    End Property

    ''' <summary>
    '''     Single Param Constructor for specifying the DB file.
    ''' </summary>
    ''' <param name="inputFile">The File containing the DB</param>
    Public Sub New(ByVal inputFile As String)
        dbConnection = String.Format("Data Source={0}", inputFile)
    End Sub

    ''' <summary>
    '''     Allows the programmer to run a query against the Database.
    ''' </summary>
    ''' <param name="sql">The SQL to run</param>
    ''' <returns>A DataTable containing the result set.</returns>
    Public Function GetDataTable(ByVal sql As String) As DataTable
        Dim dt As New DataTable()
        Dim cnn As New SQLiteConnection()
        Try
            cnn.ConnectionString = dbConnection
            cnn.Open()
            Dim mycommand As New SQLiteCommand(cnn)
            mycommand.CommandText = sql
            Dim reader As SQLiteDataReader = mycommand.ExecuteReader()
            dt.Load(reader)
            reader.Close()
        Catch e As Exception
            Throw New Exception(e.Message)
        Finally
            cnn.Close()
        End Try
        Return dt
    End Function
    ''' <summary>
    ''' Optional a As Currency = 0, 
    ''' Optional b As String = "",  ????????????
    ''' Optional BB As String = "",  ???????????? 
    ''' Optional f As String = "", 
    ''' Optional c As Long = 0, 
    ''' Optional D As Byte = 0, 
    ''' Optional e As Long = 0, 
    ''' Optional r As Long = 0, 
    ''' Optional p As Byte = 0, 
    ''' Optional q As Byte = 0, 
    ''' Optional i As Currency = 0, 
    ''' Optional j As Long = 0, 
    ''' Optional k As Long = 0, 
    ''' Optional L As Long = 0, 
    ''' Optional m As Long = 0, 
    ''' Optional s As String = "", 
    ''' Optional t As String = "", 
    ''' Optional u As String = "", 
    ''' Optional v As String = "", 
    ''' Optional w As String = "", 
    ''' Optional x As Byte = 0, 
    ''' Optional y As Byte = 0, 
    ''' Optional z As Byte = 0, 
    ''' Optional g As Integer = 0, 
    ''' Optional H As Integer = 0, _
    ''' Optional bTransaction As Boolean = True, 
    ''' Optional bExec As Boolean = True, 
    ''' Optional bReplace As Boolean = True, 
    ''' Optional sDb As String = ""
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function createTable() As Boolean
        Dim Sql As String = "CREATE TABLE IF NOT EXISTS tt (" & _
                            " a INTEGER PRIMARY KEY AUTOINCREMENT, " & _
                            " c INTEGER default 0, e INTEGER default 0, r INTEGER default 0, d INTEGER default 0, " & _
                            " p INTEGER default 0, q INTEGER default 0, " & _
                            " b TEXT, f TEXT, " & _
                            " i INTEGER default 0, j INTEGER default 0, k INTEGER default 0, l INTEGER default 0, m INTEGER default 0, " & _
                            " x INTEGER default 0, y INTEGER default 0, z INTEGER default 0, " & _
                            " s TEXT, t TEXT, u TEXT, v TEXT, w TEXT, " & _
                            " g INTEGER default 0, h INTEGER default 0 ); "
        Try
            Me.ExecuteNonQuery(Sql)
            Sql = "CREATE INDEX IF NOT EXISTS x_p ON tt (d,p);" & _
                  "CREATE INDEX IF NOT EXISTS x_f ON tt (d,f);" & _
                  "CREATE INDEX IF NOT EXISTS x_r ON tt (r);" & _
                  "CREATE INDEX IF NOT EXISTS x_e ON tt (e);" & _
                  "CREATE INDEX IF NOT EXISTS x_g ON tt (d,g);" & _
                  "CREATE INDEX IF NOT EXISTS x_i ON tt (d,i);" & _
                  "CREATE INDEX IF NOT EXISTS x_j ON tt (d,j);" & _
                  "CREATE INDEX IF NOT EXISTS x_k ON tt (d,k);"
            Return True
        Catch
            Return False
        End Try
    End Function

    ''' <summary>
    '''     Allows the programmer to interact with the database for purposes other than a query.
    ''' </summary>
    ''' <param name="sql">The SQL to be run.</param>
    ''' <returns>An Integer containing the number of rows updated.</returns>
    Public Function ExecuteNonQuery(ByVal sql As String) As Integer
        Dim cnn As New SQLiteConnection()
        Try
            cnn.ConnectionString = dbConnection
            cnn.Open()
            Dim mycommand As New SQLiteCommand(cnn)
            mycommand.CommandText = sql
            Dim rowsUpdated As Integer = mycommand.ExecuteNonQuery()
            cnn.Close()
            Return rowsUpdated
        Catch ex As Exception
            Throw New Exception(ex.Message)
        Finally
            cnn.Close()
        End Try

    End Function

    ''' <summary>
    '''     Allows the programmer to retrieve single items from the DB.
    ''' </summary>
    ''' <param name="sql">The query to run.</param>
    ''' <returns>A string.</returns>
    Public Function ExecuteScalar(ByVal sql As String) As String
        Dim cnn As New SQLiteConnection()
        Try
            cnn.ConnectionString = dbConnection
            cnn.Open()
            Dim mycommand As New SQLiteCommand(cnn)
            mycommand.CommandText = sql
            Dim value As Object = mycommand.ExecuteScalar()
            If value <> Nothing Then
                Return value.ToString()
            End If
            Return ""
        Catch ex As Exception
            Throw New Exception(ex.Message)
        Finally
            cnn.Close()
        End Try

    End Function

    ''' <summary>
    '''     Allows the programmer to easily update rows in the DB.
    ''' </summary>
    ''' <param name="tableName">The table to update.</param>
    ''' <param name="data">A dictionary containing Column names and their new values.</param>
    ''' <param name="where">The where clause for the update statement.</param>
    ''' <returns>A boolean true or false to signify success or failure.</returns>
    Public Function Update(ByVal tableName As String, ByVal data As Dictionary(Of String, String), ByVal where As String) As Boolean
        Dim vals As String = ""
        Dim returnCode As Boolean = True
        If data.Count >= 1 Then
            For Each val As KeyValuePair(Of String, String) In data
                vals += String.Format(" {0} = '{1}',", val.Key.ToString(), val.Value.ToString())
            Next
            vals = vals.Substring(0, vals.Length - 1)
        End If
        Try
            Me.ExecuteNonQuery(String.Format("update {0} set {1} where {2};", tableName, vals, where))
        Catch
            returnCode = False
        End Try
        Return returnCode
    End Function

    ''' <summary>
    '''     Allows the programmer to easily update rows in the DB for table "tt".
    ''' </summary>
    ''' <param name="data">A dictionary containing Column names and their new values.</param>
    ''' <param name="where">The where clause for the update statement.</param>
    ''' <returns>A boolean true or false to signify success or failure.</returns>
    Public Function Update(ByVal data As Dictionary(Of String, String), ByVal where As String) As Boolean
        Return Me.Update("tt", data, where)
    End Function

    ''' <summary>
    '''     Allows the programmer to easily delete rows from the DB.
    ''' </summary>
    ''' <param name="tableName">The table from which to delete.</param>
    ''' <param name="where">The where clause for the delete.</param>
    ''' <returns>A boolean true or false to signify success or failure.</returns>
    Public Function Delete(ByVal tableName As String, ByVal where As String) As Boolean
        Dim returnCode As Boolean = True
        Try
            Me.ExecuteNonQuery(String.Format("delete from {0} where {1};", tableName, where))
        Catch fail As Exception
            MessageBox.Show(fail.Message)
            returnCode = False
        End Try
        Return returnCode
    End Function

    ''' <summary>
    '''     Allows the programmer to easily delete rows from the DB for table "tt".
    ''' </summary>
    ''' <param name="where">The where clause for the delete.</param>
    ''' <returns>A boolean true or false to signify success or failure.</returns>
    Public Function Delete(ByVal where As String) As Boolean
        Return Delete("tt", where)
    End Function

    ''' <summary>
    '''     Allows the programmer to easily insert into the DB
    ''' </summary>
    ''' <param name="tableName">The table into which we insert the data.</param>
    ''' <param name="data">A dictionary containing the column names and data for the insert.</param>
    ''' <returns>A boolean true or false to signify success or failure.</returns>
    Public Function Insert(ByVal tableName As String, ByVal data As Dictionary(Of String, String)) As Boolean
        Dim columns As String = ""
        Dim values As String = ""
        Dim sql As String = ""
        Dim returnCode As Boolean = True
        For Each val As KeyValuePair(Of String, String) In data
            Select Case val.Key.ToString()
                Case "b", "f", "s", "t", "u", "v", "w"
                    columns += String.Format(" {0},", val.Key.ToString())
                    values += String.Format(" {0},", "@" & val.Key.ToString())
                Case Else
                    columns += String.Format(" {0},", val.Key.ToString())
                    values += String.Format(" {0},", val.Value.ToString())
            End Select
        Next
        columns = columns.Substring(0, columns.Length - 1)
        values = values.Substring(0, values.Length - 1)
        sql = String.Format("insert into {0} ({1}) values({2});", tableName, columns, values)

        Dim cnn As New SQLiteConnection()
        Try
            cnn.ConnectionString = dbConnection
            cnn.Open()
            Dim mycommand As New SQLiteCommand(cnn)
            mycommand.CommandText = sql
            Dim paramenter As New SQLite.SQLiteParameter
            paramenter.DbType = DbType.AnsiString
            For Each val As KeyValuePair(Of String, String) In data
                Select Case val.Key.ToString()
                    Case "b", "f", "s", "t", "u", "v", "w"
                        mycommand.Parameters.AddWithValue("@" & val.Key.ToString(), val.Value.ToString())
                    Case Else
                End Select
            Next

            Dim rowsUpdated As Integer = mycommand.ExecuteNonQuery()
            Return rowsUpdated
        Catch ex As Exception
            Throw New Exception(ex.Message)
        Finally
            cnn.Close()
        End Try
        
        Return returnCode
    End Function

    ''' <summary>
    '''     Allows the programmer to easily insert into the DB for table "tt"
    ''' </summary>
    ''' <param name="data">A dictionary containing the column names and data for the insert.</param>
    ''' <returns>A boolean true or false to signify success or failure.</returns>
    Public Function Insert(ByVal data As Dictionary(Of String, String)) As Boolean
        Return Me.Insert("tt", data)
    End Function


    ''' <summary>
    '''     Allows the programmer to easily delete all data from the DB.
    ''' </summary>
    ''' <returns>A boolean true or false to signify success or failure.</returns>
    Public Function ClearDB() As Boolean
        Dim tables As DataTable
        Try
            tables = Me.GetDataTable("select NAME from SQLITE_MASTER where type='table' order by NAME;")
            For Each table As DataRow In tables.Rows
                Me.ClearTable(table("NAME").ToString())
            Next
            Return True
        Catch
            Return False
        End Try
    End Function

    ''' <summary>
    '''     Allows the user to easily clear all data from a specific table.
    ''' </summary>
    ''' <param name="table">The name of the table to clear.</param>
    ''' <returns>A boolean true or false to signify success or failure.</returns>
    Public Function ClearTable(ByVal table As String) As Boolean
        Try

            Me.ExecuteNonQuery(String.Format("delete from {0};", table))
            Return True
        Catch
            Return False
        End Try
    End Function

    ''' <summary>
    '''     Allows the user to easily clear all data from a specific table "tt".
    ''' </summary>
    ''' <returns>A boolean true or false to signify success or failure.</returns>
    Public Function ClearTable() As Boolean
        Return ClearTable("tt")
    End Function

End Class
